﻿Imports System.Math
Imports System.DBNull
Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class CustoDeImportacaoDespAduaneirasLData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            Dim strConnection As String
            strConnection = "A"

            lblModulo.Text = "Operações"
            lblModuloItem.Text = "Dados da Importação - Despesas Aduaneiras - LINHA"

            If (LoadHolding(strConnection) = True) Then

                FillDDL()

                Try

                    ddlCompanhia.Enabled = False
                    ddlEstabelecimento.Enabled = False
                    ddlFornecedorServico.Enabled = False
                    txtNumeroDocumento.Enabled = False
                    ddlDI.Enabled = False
                    txtDataDocumento.Enabled = False
                    txtDataRegistro.Enabled = False
                    ddlMoeda.Enabled = False

                    If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir


                    End If

                    ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
                    FillDDLEstabelecimento()

                    ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
                    LoadUFDestino()

                    ddlFornecedorServico.SelectedValue = Session("strCodFornecedorServico").ToString
                    LoadUFOrigem()

                    txtNumeroDocumento.Text = Session("strNumeroDocumento").ToString
                    txtDataDocumento.Text = Session("strDataDocumento").ToString

                    'FillDDLDI()
                    ddlDI.Text = Session("strDI").ToString

                    txtDataRegistro.Text = Session("strDataRegistro")
                    ddlMoeda.SelectedValue = Session("strMoeda")

                    If (Session("strOperacao") = "I") Then
                        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

                            Dim theQueryString As String = "SELECT ValorTaxaConversao " & _
                                                    "FROM custoimportacao  " & _
                                                    "WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "' " & _
                                                    "AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' " & _
                                                    "AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                                                    "AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "'"

                            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
                            theSqlCommand.CommandText = theQueryString

                            Try
                                theSqlConnection.Open()
                                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                                If (theSqlDataReader.Read() = True) Then
                                    lblTaxaConversao.Text = theSqlDataReader(0)
                                Else
                                    lblMsg.Text = "Erro no valor da taxa de conversão."
                                End If
                                theSqlDataReader.Close()
                            Catch ex As Exception
                                'theSqlDataReader.Close()
                                lblMsg.Text = "Msg DB: " & ex.Message
                            End Try

                        End Using
                    ElseIf (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                        LoadData()
                    End If

                    If (Session("strOperacao") = "C") Then ' Consultar
                        'btnIncluir.Enabled = False
                    End If

                Catch ex As Exception
                    lblMsg.Text = ex.Message
                End Try

            End If

        End If

    End Sub

    ' Step 02
    Private Function LoadHolding(ByVal strConn As String) As Boolean

        ' CAMG Informática | Marcelo Barcelos | 26.06.2010 08h


        LoadHolding = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT CodigoDaHolding, NomeDaHolding" & _
                         " FROM Holding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    lblHolding.Text = theSqlDataReader(1)
                    lblHolding.ToolTip = theSqlDataReader(0)
                    LoadHolding = True
                Else
                    lblMsg.Text = "Holding não cadastrada no Sistema."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Function

    '' Step 03
    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, NomeDaCia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "NomeDaCia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedorServico, RazaoSocialFornecedorServico AS FornecedorServico" & _
                         " FROM fornecedorservico WHERE CodTipoFornecedorServico = 2" & _
                         " ORDER BY RazaoSocialFornecedorServico;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlFornecedorServico.DataTextField = "FornecedorServico"
                ddlFornecedorServico.DataValueField = "CodFornecedorServico"
                ddlFornecedorServico.DataSource = mydatatable
                ddlFornecedorServico.DataBind()

                ddlFornecedorServico.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaMoeda, SiglaDaMoeda + ' - ' + NomeDaMoeda AS Moeda" & _
                 " FROM Moeda" & _
                 " ORDER BY SiglaDaMoeda;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMoeda.DataTextField = "Moeda"
                ddlMoeda.DataValueField = "CodigoDaMoeda"
                ddlMoeda.DataSource = mydatatable
                ddlMoeda.DataBind()

                ddlMoeda.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodFornecedorServico, RazaoSocialFornecedorServico AS FornecedorServico" & _
                         " FROM fornecedorservico" & _
                         " ORDER BY RazaoSocialFornecedorServico;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)

                ddlFornecedorServicoExecutor.DataTextField = "FornecedorServico"
                ddlFornecedorServicoExecutor.DataValueField = "CodFornecedorServico"
                ddlFornecedorServicoExecutor.DataSource = mydatatable
                ddlFornecedorServicoExecutor.DataBind()

                ddlFornecedorServicoExecutor.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodTipoDespesa, CASE WHEN Valor = 'N' THEN  DscTipoDespesa + ' ' + '(CR)' ELSE DscTipoDespesa + ' ' + '(DB)' END AS TipoDespesa" & _
                 " FROM TipoDespesa" & _
                 " WHERE CodTipoOperacao = 'E'" & _
                 " ORDER BY TipoDespesa;"


            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlTipoDespesa.DataTextField = "TipoDespesa"
                ddlTipoDespesa.DataValueField = "CodTipoDespesa"
                ddlTipoDespesa.DataSource = mydatatable
                ddlTipoDespesa.DataBind()

                ddlTipoDespesa.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 04
    Private Sub LoadData()

        Dim intQtdeItems As Integer = 0

        ddlCompanhia.Enabled = False
        ddlEstabelecimento.Enabled = False
        ddlFornecedorServico.Enabled = False
        txtNumeroDocumento.Enabled = False
        ddlDI.Enabled = False
        txtDataDocumento.Enabled = False
        txtDataRegistro.Enabled = False
        ddlMoeda.Enabled = False

        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir


        End If

        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString
        FillDDLEstabelecimento()

        ddlEstabelecimento.SelectedValue = Session("strCodigoDoEstabelecimento").ToString
        LoadUFDestino()

        ddlFornecedorServico.SelectedValue = Session("strCodFornecedorServico").ToString
        LoadUFOrigem()

        txtNumeroDocumento.Text = Session("strNumeroDocumento").ToString
        txtDataDocumento.Text = Session("strDataDocumento").ToString

        'FillDDLDI()
        ddlDI.Text = Session("strDI").ToString

        txtDataRegistro.Text = Session("strDataRegistro")
        ddlMoeda.SelectedValue = Session("strMoeda")
        lblLinhaCustoImportacao.Text = Session("strLinhaCustoImportacao")



        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " CodSubContratante, CodTipoOperacao, CodTipoDespesa, ValorDespesa"

            theQueryString = theQueryString & _
                     " FROM custoimportacaodata" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "' AND LinhaCustoImportacao = " & lblLinhaCustoImportacao.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()


                    ddlFornecedorServicoExecutor.SelectedValue = theSqlDataReader!CodSubContratante
                    ddlTipoDespesa.SelectedValue = theSqlDataReader!CodTipoDespesa
                    txtVlDespesa.Text = theSqlDataReader!ValorDespesa

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT ValorTaxaConversao " & _
                                    "FROM custoimportacao  " & _
                                    "WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "' " & _
                                    "AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' " & _
                                    "AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                                    "AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "'"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    lblTaxaConversao.Text = theSqlDataReader(0)
                Else
                    lblMsg.Text = "Erro no valor da taxa de conversão."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub FillDDLEstabelecimento()

        ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDoEstabelecimento, CodigoDoEstabelecimento + ' - ' + RazaoSocialDoEstabelecimento AS Estabelecimento" & _
                     " FROM Estabelecimento" & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " ORDER BY CodigoDoEstabelecimento;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlEstabelecimento.DataTextField = "Estabelecimento"
                ddlEstabelecimento.DataValueField = "CodigoDoEstabelecimento"
                ddlEstabelecimento.DataSource = mydatatable
                ddlEstabelecimento.DataBind()

                ddlEstabelecimento.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    '' Step 07
    'Private Sub FillDDLDI()

    '    ' CAMG Informática | Marcelo Barcelos | 05.01.2010 16h45

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Dim mydatatable As New DataTable
    '        Dim theQueryString As String

    '        theQueryString = "SELECT DI" & _
    '                 " FROM DIHeader" & _
    '                 " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
    '                 " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
    '                 " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'"

    '        If (Session("strOperacao") = "I") Then ' Incluir
    '            theQueryString = theQueryString & _
    '                     " AND CodSituacaoDI IN (1, 2)" & _
    '                     " AND DI NOT IN (SELECT DI FROM ControleDespesasHeader WHERE di is NOT NULL)"
    '        End If

    '        theQueryString = theQueryString & _
    '                 " ORDER BY DI;"

    '        Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
    '        theSqlCommand.CommandText = theQueryString

    '        Try
    '            theSqlConnection.Open()
    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            mydatatable.Load(theSqlDataReader)
    '            ddlDI.DataTextField = "DI"
    '            ddlDI.DataValueField = "DI"
    '            ddlDI.DataSource = mydatatable
    '            ddlDI.DataBind()

    '            ddlDI.Items.Insert(0, "")
    '            theSqlDataReader.Close()
    '        Catch ex As Exception
    '            'theSqlDataReader.Close()
    '            lblMsg.Text = "Msg DB: " & ex.Message
    '        End Try

    '    End Using

    'End Sub

    Private Sub LoadUFDestino()

        lblUFDestino.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT A.CodigoDaUF, B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM Estabelecimento A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND A.CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If Not (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblUFDestino.Text = theSqlDataReader!SiglaDaUF
                        lblUFDestino.ToolTip = theSqlDataReader!CodigoDaUF
                    Else
                        lblMsg.Text = "Estabelecimento sem UF informada."
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Sub

    Private Sub LoadUFOrigem()


        lblUFOrigem.Text = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            Dim theQueryString As String = "SELECT" & _
                     " A.CodigoDaUF," & _
                     " B.SiglaDaUF"

            theQueryString = theQueryString & _
                     " FROM FornecedorServico A, uf B" & _
                     " WHERE A.CodigoDaUF = B.CodigoDaUF AND A.CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    If (IsDBNull(theSqlDataReader!SiglaDaUF) = True) Then
                        lblMsg.Text = "Despachante sem UF informada."
                    Else
                        lblUFOrigem.Text = theSqlDataReader!SiglaDaUF
                        lblUFOrigem.ToolTip = theSqlDataReader!CodigoDaUF
                    End If

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub


    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Session.Add("strOperacao", "A") ' Vazio

        Session.Add("strHolding", lblHolding.Text)
        Session.Add("strCodigoDaHolding", lblHolding.ToolTip)
        Session.Add("strCodigoDaCia", HttpUtility.HtmlDecode(ddlCompanhia.SelectedValue))
        Session.Add("strCodigoDoEstabelecimento", HttpUtility.HtmlDecode(ddlEstabelecimento.SelectedValue))
        Session.Add("strCodFornecedorServico", HttpUtility.HtmlDecode(ddlFornecedorServico.SelectedValue))
        Session.Add("strNumeroDocumento", HttpUtility.HtmlDecode(txtNumeroDocumento.Text))
        Session.Add("strDataDocumento", HttpUtility.HtmlDecode(txtDataDocumento.Text))
        Session.Add("strDI", HttpUtility.HtmlDecode(ddlDI.Text))
        Session.Add("strDataRegistro", HttpUtility.HtmlDecode(txtDataRegistro.Text))
        Session.Add("strMoeda", HttpUtility.HtmlDecode(ddlMoeda.SelectedValue))

        Response.Redirect("CustoDeImportacaoDespAduaneirasL.aspx")

    End Sub


    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            'If (CodeExist(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            If Not (CreateSequence(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            'If (VerifyData(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If


        Dim nValorDespesa As String
        Dim nValorDespesaUSD As String

        nValorDespesa = ""
        nValorDespesaUSD = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT SUM(ValorDespesa) AS ValorDespesa, SUM(ValorDespesaUSD) AS ValorDespesaUSD " & _
                                    "FROM custoimportacaodata  " & _
                                    "WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "' " & _
                                    "AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "' " & _
                                    "AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "' " & _
                                    "AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "'"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read() = True) Then
                    nValorDespesa = theSqlDataReader(0)
                    nValorDespesaUSD = theSqlDataReader(1)
                Else
                    lblMsg.Text = "Erro no calculo do valoe da despesa."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            nValorDespesa = Replace(nValorDespesa, ",", ".")
            nValorDespesaUSD = Replace(nValorDespesaUSD, ",", ".")


            Dim theQueryString As String
            theQueryString = "UPDATE custoimportacao SET" & _
                     " VlTotDespesas = " & nValorDespesa & ", VlTotDespesaUSD = " & nValorDespesaUSD & ","

            theQueryString = theQueryString & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nLastUpdated & ""

            theQueryString = theQueryString & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    Private Function DataValid() As Boolean

        DataValid = False

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (ddlEstabelecimento.Text = "") Then
            lblMsg.Text = "Estabelecimento deve ser selecionado."
            Exit Function
        End If

        If (ddlFornecedorServico.Text = "") Then
            lblMsg.Text = "Despachante deve ser selecionado."
            Exit Function
        End If

        If (Trim(txtNumeroDocumento.Text) = "") Then
            lblMsg.Text = "Número do Documento deve ser preenchido."
            Exit Function
        End If

        If (Trim(txtDataDocumento.Text) = "") Then
            lblMsg.Text = "Data do Documento deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataDocumento.Text) = True Then
            lblMsg.Text = "Data do Documento inválida."
            Exit Function
        End If

        'If (Trim(txtDataLancamento.Text) = "") Then
        '    lblMsg.Text = "Data de Lançamento deve ser preenchida."
        '    Exit Function
        'End If

        'If Not IsDate(txtDataLancamento.Text) = True Then
        '    lblMsg.Text = "Data de Lançamento inválida."
        '    Exit Function
        'End If

        'If (CDate(txtDataLancamento.Text) < txtDataDocumento.Text) Then
        '    lblMsg.Text = "Data de Lançamento deve ser igual ou posterior à Data do Documento."
        '    Exit Function
        'End If

        If (ddlDI.Text = "") Then
            lblMsg.Text = "DI deve ser selecionada."
            Exit Function
        End If


        DataValid = True

    End Function

    Private Function CreateSequence(ByVal strConn As String) As Boolean

        CreateSequence = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT MAX(LinhaCustoImportacao) AS MAX_LinhaCustoImportacao" & _
                         " FROM custoimportacaodata" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                         " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.HasRows = True) Then
                    ' Loop through all the rows in the DataTableReader.
                    Do While theSqlDataReader.Read()
                        If Not (IsDBNull(theSqlDataReader(0)) = True) Then
                            lblLinhaCustoImportacao.Text = (theSqlDataReader(0) + 1)
                        Else
                            lblLinhaCustoImportacao.Text = 1
                        End If
                    Loop
                Else
                    lblLinhaCustoImportacao.Text = 1
                End If

                CreateSequence = True

                theSqlDataReader.Close()

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim strDataDocumento As String
        Dim strDataRegistro As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String
        Dim strValorDespesaUSD As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'strDataDocumento = Format(CDate(txtDataDocumento.Text), "yyyy-MM-dd HH:mm:ss")
            'strDataLancamento = Format(CDate(txtDataLancamento.Text), "yyyy-MM-dd HH:mm:ss")

            strDataDocumento = "'" & FormatMediumDate(txtDataDocumento.Text) & "'"
            'strDataLancamento = "'" & FormatMediumDate(txtDataLancamento.Text) & "'"
            strDataRegistro = "'" & FormatMediumDate(txtDataRegistro.Text) & "'"


            strValorDespesaUSD = Round(CDbl(txtVlDespesa.Text / lblTaxaConversao.Text), 2)

            strValorDespesaUSD = Replace(strValorDespesaUSD, ",", ".")
            txtVlDespesa.Text = Replace(txtVlDespesa.Text, ",", ".")


            'txtValorTotalDespesasICMS.Text = Replace(txtValorTotalDespesasICMS.Text, ",", ".")
            'txtValorOutrosDebitos.Text = Replace(txtValorOutrosDebitos.Text, ",", ".")
            'txtValorOutrosCreditos.Text = Replace(txtValorOutrosCreditos.Text, ",", ".")
            'txtValorTSA.Text = Replace(txtValorTSA.Text, ",", ".")
            'txtValorFTI.Text = Replace(txtValorFTI.Text, ",", ".")

            Dim theQueryString As String
            theQueryString = "INSERT INTO custoimportacaodata" & _
                     " (" & _
                     " CodigoDaHolding," & _
                     " CodigoDaCia," & _
                     " CodigoDoEstabelecimento," & _
                     " CodFornecedorServico," & _
                     " NumeroDocumento," & _
                     " DataDocumento," & _
                     " DI," & _
                     " DataRegistro," & _
                     " CodMoeda," & _
                     " LinhaCustoImportacao," & _
                     " CodSubContratante," & _
                     " CodTipoOperacao," & _
                     " CodTipoDespesa," & _
                     " ValorDespesa," & _
                     " ValorDespesaUSD," & _
                     " CreateUser," & _
                     " Created" & _
                     " )"

            theQueryString = theQueryString & _
                     " VALUES " & _
                     " (" & _
                     " '" & lblHolding.ToolTip & "'," & _
                     " '" & ddlCompanhia.SelectedValue & "'," & _
                     " '" & ddlEstabelecimento.SelectedValue & "'," & _
                     " '" & ddlFornecedorServico.SelectedValue & "'," & _
                     " '" & txtNumeroDocumento.Text & "'," & _
                     " " & strDataDocumento & "," & _
                     " '" & ddlDI.Text & "'," & _
                     " " & strDataRegistro & "," & _
                     " '" & ddlMoeda.SelectedValue & "'," & _
                     " " & lblLinhaCustoImportacao.Text & "," & _
                     " '" & ddlFornecedorServicoExecutor.SelectedValue & "'," & _
                     " 'E'," & _
                     " '" & ddlTipoDespesa.SelectedValue & "'," & _
                     " " & txtVlDespesa.Text & "," & _
                     " " & strValorDespesaUSD & "," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function


    Private Function EditRecord(ByVal strConn As String) As Boolean

        Dim strDataDocumento As String
        Dim strDataRegistro As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String
        Dim strVlDespesa As String
        Dim strValorDespesaUSD As String

        If Not IsNumeric(txtVlDespesa.Text) = True Then
            strValorDespesaUSD = "Null"
            strVlDespesa = "Null"
        Else
            strValorDespesaUSD = Round(CDbl(txtVlDespesa.Text / lblTaxaConversao.Text), 2)
            strValorDespesaUSD = Replace(strValorDespesaUSD, ",", ".")
            strVlDespesa = Replace(txtVlDespesa.Text, ",", ".")
        End If


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        EditRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)


            strDataDocumento = "'" & FormatMediumDate(txtDataDocumento.Text) & "'"
            strDataRegistro = "'" & FormatMediumDate(txtDataRegistro.Text) & "'"


            Dim theQueryString As String
            theQueryString = "UPDATE custoimportacaodata SET" & _
                     " CodSubContratante = '" & ddlFornecedorServicoExecutor.SelectedValue & "'," & _
                     " CodTipoOperacao = 'E'," & _
                     " CodTipoDespesa = '" & ddlTipoDespesa.SelectedValue & "'," & _
                     " ValorDespesa = " & strVlDespesa & "," & _
                     " ValorDespesaUSD = " & strValorDespesaUSD & ","
            theQueryString = theQueryString & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nLastUpdated & ""

            theQueryString = theQueryString & _
                     " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                     " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                     " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "' AND LinhaCustoImportacao = " & lblLinhaCustoImportacao.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                EditRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        DeleteRecord = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM custoimportacaodata" & _
                         " WHERE CodigoDaHolding = '" & lblHolding.ToolTip & "'" & _
                         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
                         " AND CodFornecedorServico = '" & ddlFornecedorServico.SelectedValue & "'" & _
                         " AND NumeroDocumento = '" & UCase(Trim(txtNumeroDocumento.Text)) & "' AND LinhaCustoImportacao = " & lblLinhaCustoImportacao.Text & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Public Function FormatMediumDate(ByVal DateValue As String) As String

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function


End Class
